----------------------------[ exceptions ]----------------------------------

/*
conn s1b/11@10;
*/
--Å×ÀÌºí »ý¼º
drop   table exceptions cascade constraints purge ;
create table exceptions (err_rowid  rowid
                        ,owner      varchar2(20)
	                ,table_name varchar2(20)
		        ,constraint varchar2(30));



----------------------------[ check constraints ]----------------------------------


select owner ,TABLE_NAME, Lpad(CONSTRAINT_NAME,20,' '), CONSTRAINT_TYPE, STATUS
  from  user_constraints 
  where CONSTRAINT_NAME like '%FK' ; 
--  and TABLE_NAME in ('EMP','DEPT');

/*
S1B	EMP	                EMP_EMP_FK	         R	ENABLED
S1B	EMP	                EMP_DEPT_FK	R	ENABLED
*/



----------------------------[ disable constraints ]----------------------------------

/*
select Q'[alter table ]'||Rpad(TABLE_NAME,13,' ')
     ||Q'[ disable constraint ]'||Lpad(CONSTRAINT_NAME,27,' ')||Q'[;]'
  from user_constraints  
  where CONSTRAINT_NAME like '%FK';
*/

alter table EMP           disable constraint                 EMP_DEPT_FK;
alter table EMP           disable constraint                 EMP_EMP_FK;



------------[ truncate table ]------------------------------

/*
select Q'[truncate table ]'||TNAME ||Q'[ ;]'  from tab;
*/

truncate table DEPT ;
truncate table EMP ;
truncate table EXCEPTIONS ;
truncate table SALGRADE ;



---------------  select * from DEPT;  4 row-------------------------------

/*
select Q'[insert into DEPT values(]'
||Lpad(deptno,3,' ') ||Q'[,]' 
||Rpad(Q'[']'|| dNAME||Q'[']',14,' ')  ||Q'[,]' 
||Rpad(Q'[']'|| Loc  ||Q'[']',11,' ')  ||Q'[);]' 
from DEPT ;
*/

insert into DEPT values( 10,'ACCOUNTING'  ,'NEW YORK' );
insert into DEPT values( 20,'RESEARCH'    ,'DALLAS'   );
insert into DEPT values( 30,'SALES'       ,'CHICAGO'  );
insert into DEPT values( 40,'OPERATIONS'  ,'BOSTON'   );
commit;



--------------  select * from EMP;  14 row------------------

/*
select Q'[insert into EMP values(]'
||Lpad(empno,5,' ') ||Q'[,]' 
||Rpad(Q'[']'|| eNAME||Q'[']',9,' ')  ||Q'[,]' 
||Rpad(Q'[']'|| job  ||Q'[']',11,' ')  ||Q'[,]'  
||Lpad( nvl(to_char(mgr),'null') ,5,' ')  ||Q'[,]'  
||Rpad(Q'[']'|| hiredate  ||Q'[']',10,' ')  ||Q'[,]'  
||Lpad( sal ,5,' ')  ||Q'[,]'  
||Lpad( nvl(to_char(comm)  ,'null') ,5,' ')  ||Q'[,]'  
||Lpad( nvl(to_char(deptno),'null') ,4,' ')  
||Q'[);]' copy_run from EMP ;
*/

insert into EMP values( 7369,'SMITH'  ,'CLERK'    , 7902,'80/11/17',  800, null,  20);
insert into EMP values( 7499,'ALLEN'  ,'SALESMAN' , 7698,'81/02/20', 1600,  300,  30);
insert into EMP values( 7521,'WARD'   ,'SALESMAN' , 7698,'81/02/22', 1250,  500,  30);
insert into EMP values( 7566,'JONES'  ,'MANAGER'  , 7839,'81/04/02', 2975, null,  20);
insert into EMP values( 7654,'MARTIN' ,'SALESMAN' , 7698,'81/09/28', 1250, 1400,  30);
insert into EMP values( 7698,'BLAKE'  ,'MANAGER'  , 7839,'81/05/01', 2850, null,  30);
insert into EMP values( 7782,'CLARK'  ,'MANAGER'  , 7839,'81/06/09', 2450, null,  10);
insert into EMP values( 7788,'SCOTT'  ,'ANALYST'  , 7566,'82/12/09', 3000, null,  20);
insert into EMP values( 7839,'KING'   ,'PRESIDENT', null,'81/11/17', 5000, null,  10);
insert into EMP values( 7844,'TURNER' ,'SALESMAN' , 7698,'81/09/08', 1500,    0,  30);
insert into EMP values( 7876,'ADAMS'  ,'CLERK'    , 7788,'83/01/12', 1100, null,  20);
insert into EMP values( 7900,'JAMES'  ,'CLERK'    , 7698,'81/12/03',  950, null,  30);
insert into EMP values( 7902,'FORD'   ,'ANALYST'  , 7566,'81/12/03', 3000, null,  20);
insert into EMP values( 7934,'MILLER' ,'CLERK'    , 7782,'82/01/23', 1300, null,  10);
--insert into EMP values( 1111,'MILLER' ,'CLERK'    , 1111,'82/01/23', 1300, null,  44);


--------------  select * from SALGRADE;  5 row------------------

/*
select Q'[insert into SALGRADE values(]'
||Lpad( grade,2,' ')  ||Q'[,]' 
||Lpad( nvl(to_char(losal),'null') ,5,' ')  ||Q'[,]'  
||Lpad( nvl(to_char(hisal),'null') ,5,' ')  
||Q'[);]' copy_run from SALGRADE ;
*/

insert into SALGRADE values( 1,  700, 1200);
insert into SALGRADE values( 2, 1201, 1400);
insert into SALGRADE values( 3, 1401, 2000);
insert into SALGRADE values( 4, 2001, 3000);
insert into SALGRADE values( 5, 3001, 9999);


----------------------------[ enable constraints ]----------------------------------

/*
select  Q'[alter table ]'||Rpad(TABLE_NAME,13,' ')
      ||Q'[ enable constraint ]'||Lpad(CONSTRAINT_NAME,27,' ')
      ||Q'[ exceptions into exceptions; ]'
from  user_constraints  
where CONSTRAINT_NAME like '%FK' ;
--  and table_name in ('EMP','DEPT');  and table_name in (select tname from tab);
*/

alter table EMP           enable constraint                 EMP_DEPT_FK exceptions into exceptions; 
alter table EMP           enable constraint                  EMP_EMP_FK exceptions into exceptions; 


----------------------------[ check exceptions ]----------------------------------


select  *  from  exceptions; 
select *  from dept where rowid in (select  err_rowid  from  exceptions); 
select *  from emp  where rowid in (select  err_rowid  from  exceptions); 


/*
AABCEqAAFAAAAMHAAO	S1B	EMP	EMP_DEPT_FK
AABCEqAAFAAAAMHAAO	S1B	EMP	EMP_EMP_FK
*/




col empno       for 99999
col mgr         for 99999
col sal         for 99999
col deptno      for 99999
col ename       for a13
col job         for a10
col constraint  for a12


select e.*,ex.constraint  from emp e,exceptions ex  
where e.rowid = ex.err_rowid;

/*
 EMPNO ENAME         JOB           MGR HIREDATE        SAL       COMM DEPTNO CONSTRAINT
------ ------------- ---------- ------ ------------ ------ ---------- ------ ------------
  1111 MILLER        CLERK        1112 23-JAN-82      1300                44 EMP_DEPT_FK
  1111 MILLER        CLERK        1112 23-JAN-82      1300                44 EMP_EMP_FK
*/